﻿Public Class frmTradeBill
    Dim dsTradeBill As DataSet
    Dim daTradeBill As OleDb.OleDbDataAdapter
    Dim sql As String

    Private Sub frmTradeBill_FormClosing(sender As Object, e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
        e.Cancel = True
        Me.Hide()
    End Sub

    Private Sub frmTradeBill_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        Call gSubIconShow(Me)
        Call dbConnection()
        Call gSubMkCmbCompany("", cmbFindCompany)
        Call grid1Refresh("default")
        Call grid2Refresh("9999999999")
    End Sub

    Private Sub btnSearch_Click(sender As System.Object, e As System.EventArgs) Handles btnSearch.Click
        Call grid1Refresh("")
    End Sub

    Private Sub grdTradeBill_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles grdTradeBill.CellClick
        If e.RowIndex = -1 Then
            Exit Sub
        End If
        If IsDBNull(grdTradeBill.Rows(e.RowIndex).Cells(0).Value) = True Then
            Exit Sub
        End If
        Call grid2Refresh(grdTradeBill.Rows(e.RowIndex).Cells(10).Value)
    End Sub

    Private Sub cmbFindCompany_KeyPress(sender As Object, e As System.Windows.Forms.KeyPressEventArgs) Handles cmbFindCompany.KeyPress
        e.Handled = True
    End Sub

    Private Sub btnSearchReset_Click(sender As System.Object, e As System.EventArgs) Handles btnSearchReset.Click
        txtSearch.Clear()
        cmbFindCompany.SelectedValue = 0
        Call grid1Refresh("")
    End Sub

#Region "프로시져"

    Private Sub grid1Refresh(ByVal initFlag As String)
        Call dbConnection()
        Dim addSql As String = ""
        Dim valFindCompany As String = ""
        Dim totalAmt As Double = 0
        Dim totalOrgAmt As Double = 0
        Dim totalRestAmt As Double = 0

        If cmbFindCompany.SelectedValue.ToString <> 0 Then
            valFindCompany = cmbFindCompany.SelectedValue.ToString
        End If
        If valFindCompany <> "" Then
            addSql = addSql + " AND A.compCd=" + valFindCompany
        End If
        If txtSearch.Text <> "" Then
            addSql = addSql + " AND A.article LIKE '%" + txtSearch.Text + "%' "
        End If
        If initFlag = "default" Then
            addSql = "AND A.flag='N'"
        End If
        dsTradeBill = New DataSet

        sql = "SELECT AA.incCd, AA.incomeDt as 거래일, AA.article as 내역, AA.accountNm as 계정, AA.amount as 총액, AA.restAmount as 미수금, AA.orgAmount as 원금, AA.tax as 부가세, CC.company as 거래처, AA.remarks as 메모, AA.billCd" & _
            "   FROM (" & _
            "       SELECT A.incCd, A.incomeDt, A.article, B.accountNm, A.amount, A.restAmount, A.orgAmount, A.tax, A.flag, A.compCd, A.remarks, A.billCd " & _
            "       FROM tblIncome A LEFT JOIN tblAccount B ON A.accountCd=B.accountCd " & _
            "       WHERE A.flag='Y' AND A.billCd>0 " + addSql & _
            "       ) AA LEFT JOIN tblCompany CC ON AA.compCd=CC.compCd " & _
            "    ORDER BY AA.incomeDt DESC "

        Try
            daTradeBill = New OleDb.OleDbDataAdapter(sql, dbConn)
            daTradeBill.Fill(dsTradeBill, "TradeBill")

            grdTradeBill.DataSource = dsTradeBill.Tables("TradeBill")
            lbGrid1Cnt.Text = "총 " + dsTradeBill.Tables("TradeBill").Rows.Count.ToString + " 건"
            For c = 0 To dsTradeBill.Tables("TradeBill").Rows.Count - 1
                totalAmt = totalAmt + CDbl(dsTradeBill.Tables("TradeBill").Rows(c).Item(4))
                totalOrgAmt = totalOrgAmt + CDbl(dsTradeBill.Tables("TradeBill").Rows(c).Item(6))
                totalRestAmt = totalRestAmt + CDbl(dsTradeBill.Tables("TradeBill").Rows(c).Item(5))
            Next
            lbSummery.Text = "총합계 : " + Format(CInt(totalAmt), "#,##0")

            grdTradeBill.Columns(2).Frozen = True
            dbConn.Close()
            Call setGrid1Design()

        Catch err As ObjectDisposedException
            dbConn.Close()
            MsgBox(sql)

            MsgBox(err.Message, MsgBoxStyle.Critical, "오류")
        End Try
    End Sub

    Private Sub setGrid1Design()

        grdTradeBill.Columns(0).Visible = False
        grdTradeBill.Columns(1).Width = "80"
        grdTradeBill.Columns(2).Width = "200"
        grdTradeBill.Columns(3).Width = "80"

        grdTradeBill.Columns(4).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        grdTradeBill.Columns(4).DefaultCellStyle.Format = "n0"
        grdTradeBill.Columns(4).Width = "80"
        grdTradeBill.Columns(5).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        grdTradeBill.Columns(5).DefaultCellStyle.Format = "n0"
        grdTradeBill.Columns(5).DefaultCellStyle.ForeColor = Color.Red
        grdTradeBill.Columns(5).Width = "80"
        grdTradeBill.Columns(6).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        grdTradeBill.Columns(6).DefaultCellStyle.Format = "n0"
        grdTradeBill.Columns(6).Width = "80"
        grdTradeBill.Columns(7).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        grdTradeBill.Columns(7).DefaultCellStyle.Format = "n0"
        grdTradeBill.Columns(7).Width = "80"
        grdTradeBill.Columns(8).Width = "150"
        grdTradeBill.Columns(9).Width = "150"
        grdTradeBill.Columns(10).Visible = False
        With grdTradeBill.DefaultCellStyle
            .Alignment = DataGridViewContentAlignment.MiddleCenter

        End With
        grdTradeBill.Columns(2).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
        grdTradeBill.Columns(8).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
        grdTradeBill.Columns(9).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
        'grdTradeBill.Columns(2).Frozen = True

    End Sub


    Private Sub grid2Refresh(billCd As String)
        Dim totalAmt As Double = 0
        Call dbConnection()
        dsTradeBill = New DataSet

        sql = "SELECT AA.prdNm as 제품, AA.[output] as 수량, AA.amount as 결재금액, AA.totalPrice as 금액, CC.company as 거래처, AA.unitPrice as 단가" & _
            "   FROM (" & _
            "       SELECT B.prdNm, A.[outPut], A.totalPrice, A.amount, A.compCd, A.unitPrice " & _
            "       FROM tblPrdInOut A LEFT JOIN tblProduct B ON A.prdCd=B.prdCd " & _
            "       WHERE A.flag='Y' AND A.billCd= " + billCd & _
            "       ) AA LEFT JOIN tblCompany CC ON AA.compCd=CC.compCd " & _
            "    ORDER BY AA.prdNm "
        Try
            daTradeBill = New OleDb.OleDbDataAdapter(sql, dbConn)
            daTradeBill.Fill(dsTradeBill, "TradeBillInfo")

            grdTradeInfo.DataSource = dsTradeBill.Tables("TradeBillInfo")
            For c = 0 To dsTradeBill.Tables("TradeBillInfo").Rows.Count - 1
                totalAmt = totalAmt + CDbl(dsTradeBill.Tables("TradeBillInfo").Rows(c).Item(2))
            Next
            lbSummery2.Text = "총합계 : " + Format(CInt(totalAmt), "#,##0")

            grdTradeInfo.Columns(0).Frozen = True

            dbConn.Close()
            Call setGrid2Design()

        Catch err As ObjectDisposedException
            dbConn.Close()
            MsgBox(sql)

            MsgBox(err.Message, MsgBoxStyle.Critical, "오류")
        End Try
    End Sub

    Private Sub setGrid2Design()

        grdTradeInfo.Columns(0).Width = "120"
        grdTradeInfo.Columns(0).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft

        grdTradeInfo.Columns(1).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        grdTradeInfo.Columns(1).Width = "60"
        grdTradeInfo.Columns(2).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        grdTradeInfo.Columns(2).DefaultCellStyle.Format = "n0"
        grdTradeInfo.Columns(2).DefaultCellStyle.ForeColor = Color.Red
        grdTradeInfo.Columns(2).Width = "80"
        grdTradeInfo.Columns(3).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        grdTradeInfo.Columns(3).DefaultCellStyle.Format = "n0"
        grdTradeInfo.Columns(3).Width = "80"

        grdTradeInfo.Columns(4).Width = "150"
        grdTradeInfo.Columns(4).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft

        grdTradeInfo.Columns(5).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        grdTradeInfo.Columns(5).DefaultCellStyle.Format = "n0"
        grdTradeInfo.Columns(5).Width = "80"

    End Sub
#End Region

End Class